-- Revo.Extensions.Notifications SQL baseline schema for common providers (EF Core, EF6)
-- MSSQL version
-- version: 2

-- NOTIFICATIONS

CREATE TABLE [dbo].[RNO_NOTIFICATION_BUFFER] (
	[RNO_NBF_NotificationBufferId] [UNIQUEIDENTIFIER] NOT NULL,
	[RNO_NBF_Ordinal] [INT] IDENTITY(1,1) NOT NULL,
	[RNO_NBF_Name] [NVARCHAR] (256) NOT NULL UNIQUE,
	[RNO_NBF_PipelineName] [NVARCHAR] (MAX) NOT NULL,
	[RNO_NBF_GovernorName] [NVARCHAR] (MAX) NOT NULL
	CONSTRAINT [RNO_NOTIFICATION_BUFFER_PK] PRIMARY KEY NONCLUSTERED ([RNO_NBF_NotificationBufferId])
);

CREATE TABLE [dbo].[RNO_BUFFERED_NOTIFICATION] (
	[RNO_BNT_BufferedNotificationId] [UNIQUEIDENTIFIER] NOT NULL,
	[RNO_BNT_Ordinal] [INT] IDENTITY(1,1) NOT NULL,
	[RNO_BNT_NotificationClassName] [NVARCHAR] (MAX) NOT NULL,
	[RNO_BNT_NotificationJson] [NVARCHAR] (MAX) NOT NULL,
	[RNO_BNT_BufferId] [UNIQUEIDENTIFIER] NOT NULL,
	[RNO_BNT_TimeQueued] [DATETIMEOFFSET] NOT NULL
	CONSTRAINT [RNO_BUFFERED_NOTIFICATION_PK] PRIMARY KEY NONCLUSTERED ([RNO_BNT_BufferedNotificationId]),
	CONSTRAINT [RNO_BUFFERED_NOTIFICATION_FK_BUFFERID] FOREIGN KEY ([RNO_BNT_BufferId]) REFERENCES [dbo].[RNO_NOTIFICATION_BUFFER] ([RNO_NBF_NotificationBufferId])
);

CREATE TABLE [dbo].[RNO_APNS_USER_DEVICE_TOKEN] (
	[RNO_AUT_ApnsUserDeviceTokenId] [UNIQUEIDENTIFIER] NOT NULL,
	[RNO_AUT_Ordinal] [INT] IDENTITY(1,1) NOT NULL,
	[RNO_AUT_Version] [INT] NOT NULL,
	[RNO_AUT_UserId] [UNIQUEIDENTIFIER] NOT NULL,
	[RNO_AUT_DeviceToken] [NVARCHAR] (MAX) NOT NULL,
	[RNO_AUT_AppId] [VARCHAR] (255) NOT NULL,
	[RNO_AUT_IssuedDateTime] [DATETIMEOFFSET] NOT NULL
	CONSTRAINT [RNO_APNS_USER_DEVICE_TOKEN_PK] PRIMARY KEY NONCLUSTERED ([RNO_AUT_ApnsUserDeviceTokenId])
);

CREATE TABLE [dbo].[RNO_APNS_EXTERNAL_USER_DEVICE_TOKEN] (
	[RNO_AET_ApnsExternalUserDeviceTokenId] [UNIQUEIDENTIFIER] NOT NULL,
	[RNO_AET_Ordinal] [INT] IDENTITY(1,1) NOT NULL,
	[RNO_AET_Version] [INT] NOT NULL,
	[RNO_AET_ExternalUserId] [UNIQUEIDENTIFIER] NOT NULL,
	[RNO_AET_DeviceToken] [NVARCHAR] (MAX) NOT NULL,
	[RNO_AET_AppId] [VARCHAR] (255) NOT NULL,
	[RNO_AET_IssuedDateTime] [DATETIMEOFFSET] NOT NULL
	CONSTRAINT [RNO_APNS_EXTERNAL_USER_DEVICE_TOKEN_PK] PRIMARY KEY NONCLUSTERED ([RNO_AET_ApnsExternalUserDeviceTokenId])
);

CREATE TABLE [dbo].[RNO_FCM_USER_DEVICE_TOKEN] (
	[RNO_FUT_FcmUserDeviceTokenId] [UNIQUEIDENTIFIER] NOT NULL,
	[RNO_FUT_Ordinal] [INT] IDENTITY(1,1) NOT NULL,
	[RNO_FUT_Version] [INT] NOT NULL,
	[RNO_FUT_UserId] [UNIQUEIDENTIFIER] NOT NULL,
	[RNO_FUT_RegistrationId] [NVARCHAR] (MAX) NOT NULL,
	[RNO_FUT_AppId] [VARCHAR] (255) NOT NULL,
	[RNO_FUT_IssuedDateTime] [DATETIMEOFFSET] NOT NULL
	CONSTRAINT [RNO_FCM_USER_DEVICE_TOKEN_PK] PRIMARY KEY NONCLUSTERED ([RNO_FUT_FcmUserDeviceTokenId])
);

CREATE TABLE [dbo].[RNO_FCM_EXTERNAL_USER_DEVICE_TOKEN] (
	[RNO_FET_FcmExternalUserDeviceTokenId] [UNIQUEIDENTIFIER] NOT NULL,
	[RNO_FET_Ordinal] [INT] IDENTITY(1,1) NOT NULL,
	[RNO_FET_Version] [INT] NOT NULL,
	[RNO_FET_ExternalUserId] [UNIQUEIDENTIFIER] NOT NULL,
	[RNO_FET_RegistrationId] [NVARCHAR] (MAX) NOT NULL,
	[RNO_FET_AppId] [VARCHAR] (255) NOT NULL,
	[RNO_FET_IssuedDateTime] [DATETIMEOFFSET] NOT NULL
	CONSTRAINT [RNO_FCM_EXTERNAL_USER_DEVICE_TOKEN_PK] PRIMARY KEY NONCLUSTERED ([RNO_FET_FcmExternalUserDeviceTokenId])
);

CREATE CLUSTERED INDEX [CIX_RNO_NOTIFICATION_BUFFER] ON [dbo].[RNO_NOTIFICATION_BUFFER] ([RNO_NBF_Ordinal]);
CREATE CLUSTERED INDEX [CIX_RNO_BUFFERED_NOTIFICATION] ON [dbo].[RNO_BUFFERED_NOTIFICATION] ([RNO_BNT_Ordinal]);
CREATE CLUSTERED INDEX [CIX_RNO_APNS_USER_DEVICE_TOKEN] ON [dbo].[RNO_APNS_USER_DEVICE_TOKEN] ([RNO_AUT_Ordinal]);
CREATE CLUSTERED INDEX [CIX_RNO_APNS_EXTERNAL_USER_DEVICE_TOKEN] ON [dbo].[RNO_APNS_EXTERNAL_USER_DEVICE_TOKEN] ([RNO_AET_Ordinal]);
CREATE CLUSTERED INDEX [CIX_RNO_FCM_USER_DEVICE_TOKEN] ON [dbo].[RNO_FCM_USER_DEVICE_TOKEN] ([RNO_FUT_Ordinal]);
CREATE CLUSTERED INDEX [CIX_RNO_FCM_EXTERNAL_USER_DEVICE_TOKEN] ON [dbo].[RNO_FCM_EXTERNAL_USER_DEVICE_TOKEN] ([RNO_FET_Ordinal]);